from sqlalchemy import create_engine
from sqlalchemy import Table,Column,Integer,String,MetaData,ForeignKey
from sqlalchemy.orm import mapper
from sqlalchemy.orm import sessionmaker


engine = create_engine("sqlite:///:memory:", echo = True)
Session = sessionmaker(bind=engine)
session = Session()

metadata = MetaData()
users_table = Table("users", metadata,
                    Column("id",Integer,primary_key = True),
                    Column("name",String),
                    Column("fullname", String),
                    Column("password", String)
                    )

#create table
metadata.create_all(engine)

class User(object):
    def __init__(self,name,fullname,password):
        self.name = name
        self.fullname = fullname
        self.password = password

    def __repr__(self):
        return "<User(%s),(%s),(%s)>"%(self.name,self.fullname,self.password)


#Must map after class constructor
mapper(User,users_table)
Session.configure(bind=engine)





#Main
ed_user=User("ed","edward","shizzle")
session.add_all([User("Bill","William","nizzle"),User("Liz","Elizabeth","for")])
session.add(ed_user)

#print a single record
print ed_user.name


our_user = session.query(User).filter_by(name="ed").first()
print our_user

#Check records
print ed_user is our_user

#Update a single record
ed_user.password = "osjdhdsad03nsadnasd"

#Check what records have been edited in a session
print session.dirty

#commit changes to database
session.commit()

#check primary key
print ed_user.id



for instance in session.query(User).order_by(User.id):
    print instance.name, instance.fullname








#  LocalWords:  metadata MetaData sqlalchemy
